﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

// NOTE NOTE NOTE -- IMPORTANT 
// Need to put this using statement in yourself !!!
using System.Data.OleDb;

namespace AjaxApp
{
    public partial class Server3 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String user_input = Request.QueryString["q"];
            String return_value = "";

            try // try to open the database...
            {

                // put database information (type, location) into a string
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("\\App_Data\\myDB.accdb");
                // string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                //Server.MapPath("videoDb.accdb"); 

                // use this string (with DB info) to initialize a db connection.
                OleDbConnection con = new OleDbConnection(strCon);

                // open the database connection (would be on the 
                // database server if sql server or oracle).
                con.Open();

                // Put this next code in a try/catch since it's easy to run 
                // into errors, e.g., cant find database or table or field
                // names misspelled, or other sql syntax error.

                try
                { // to read from the database

                    // put the desired sql statement into a string
                    string sql = "SELECT customer_name FROM Customer_t where customer_name like @nameLike order by customer_name ASC";

                    // initialize a command object with the db connection
                    // info and the desired sql select statement.
                    OleDbCommand cmd = new OleDbCommand(sql, con);

                    cmd.Parameters.AddWithValue("@namelike", user_input + "%"); // replace parameter in above sql string. % is wild card character.

                    // now start executing the sql select statement
                    OleDbDataReader reader = cmd.ExecuteReader();

                    // this loop will execute one time for each row 
                    // that's returned by the sql select statement
                    if (reader.Read())
                    {
                        // reader.GetValue(0) is the first column specified in the 
                        // select statement.  (It doesnt matter what order the 
                        // fields are in design mode in MSAccess -- it only matters
                        // the order the fields were specified in your select 
                        // statement.  reader.GetValue(1) is the second column 
                        // specified in your select statement.
                        return_value += reader.GetValue(0).ToString()+"<br/>";
                    }
                    else
                    {
                        return_value += "Customer not found<br/>";
                    }

                    // close the reader (this is not as important) -- 
                    // I think it gets closed automatically when the 
                    // connection gets closed.
                    reader.Close();

                    // close the connection
                    // this is VERY important.  If you dont close your connection, 
                    // you wont be able to run your program next time. 
                    // (File will be "in use").
                    con.Close();
                } // try to get data
                catch (Exception ex)
                {
                    return_value += "Exception while running SQL:" + ex.Message + "<br/>";
                    con.Close();
                }
            } // try to open db connection
            catch (Exception ex)
            {
                return_value += "Exception while trying to open the database<br/>" + ex.Message + "<br/>";
            }
            Response.Write(return_value);
            Response.End();
        }
    }
}